from datetime import datetime
from db_conn.dbConn import conn_db,conn_db_engine
import pandas as pd
import pymysql
import time
from sqlalchemy import create_engine
from db_conn.dbConn import conn_db_engine
conn1 = conn_db_engine('iot_local')
conn2 = conn_db_engine('ryerp_local')
connect = conn_db('ryerp_local')

def update_progress(connect):
    df_batch = pd.read_sql("SELECT * FROM mm_new_production_order_assign where create_time >= '2025-02-01 00:00:00' ",conn2)
    print(df_batch,df_batch.columns)
    df_batch = df_batch[['batch_id', 'order_id', 'goods_id', 'end_date', 'loss', 'production_num', 'production_unit','create_time']]
    # # 替换所有的 NaN 值为 None
    # df_batch = df_batch.where(pd.notnull(df_batch), None)
    # ----规模较小，表中数据全删重写----#
    sql2 = 'DELETE FROM ryerp.mm_new_batch'
    cursor = connect.cursor()
    cursor.execute(sql2)
    connect.commit()
    df_batch.to_sql('mm_new_batch', con=conn2, if_exists='append', index=False)
    # ----更新子表----#
    arr_batch = df_batch['batch_id'].unique().tolist()
    str_batch_ids = ', '.join([f"'{x}'" for x in arr_batch])
    sql = f"SELECT * FROM mm_new_production_order_assign_detail WHERE batch_id IN ({str_batch_ids})"
    df_batch_detail = pd.read_sql(sql,conn2)
    df_batch_detail = df_batch_detail[['id', 'batch_id', 'goods_id', 'goods_name', 'goods_style',
        'total_num']]
    #total_num改名plan_num
    df_batch_detail = df_batch_detail.rename(columns={'total_num': 'plan_num'})
    sql2 = 'DELETE FROM ryerp.mm_new_batch_weave_progress'
    cursor = connect.cursor()
    cursor.execute(sql2)
    connect.commit()
    df_batch_detail.to_sql('mm_new_batch_weave_progress', con=conn2, if_exists='append', index=False)





def batch_progress(connect,now):
    # 获取当天日期
    if now.hour >= 7:
        zero_today = now.replace(hour=7, minute=0, second=0, microsecond=0)
    else:
        zero_today = now.replace(hour=7, minute=0, second=0, microsecond=0) - pd.Timedelta(days=1)
    today = zero_today.strftime("%Y-%m-%d")
    print(today)
    df_batch = pd.read_sql("SELECT * FROM mm_new_batch where is_finish = 0 ",conn2)
    df_batch_detail = pd.read_sql("SELECT * FROM mm_new_batch_weave_progress where is_finish = 0 ",conn2)
    # 提取batch_id到数组arr_batch
    arr_batch = df_batch['batch_id'].unique().tolist()

    # 读取袜子产量
    with connect.cursor() as cursor1:
        str_batch_ids = ', '.join([f"'{x}'" for x in arr_batch])
        sql = f"SELECT * FROM mm_new_socks_production WHERE batch_id IN ({str_batch_ids})"
        cursor1.execute(sql)
        result = cursor1.fetchall()
        df_result = pd.DataFrame(result,columns=['id', 'goods_id', 'goods_style', 'batch_id', 'num', 'date', 'device_num','capacity'])
        print(df_result,df_result.columns)

    # 创建一个cursor对象
    with connect.cursor() as cursor2:
        str_batch_ids = ', '.join([f"'{x}'" for x in arr_batch])
        sql = f"SELECT batch_id,goods_id,goods_style,plan_num FROM mm_new_production_order_assign_detail WHERE batch_id IN ({str_batch_ids})"
        cursor2.execute(sql)
        result = cursor2.fetchall()
        df_num = pd.DataFrame(result,columns=['batch_id','goods_id','goods_style','plan_num'])

    df_socks_production = df_result[['goods_id','goods_style','batch_id','num']]
    df_socks_production = df_socks_production.groupby(['batch_id','goods_id','goods_style']).sum().reset_index()
    #获取date为今天的df_socks_capacity
    date = now.strftime("%Y-%m-%d")
    df_result['date'] = df_result['date'].astype(str)
    df_socks_capacity = df_result[df_result['date'] == today][['goods_id','goods_style','batch_id','capacity','device_num']]
    df_socks = pd.merge(df_socks_production, df_socks_capacity, on=['batch_id','goods_id','goods_style'], how='left')

    df_socks.rename(columns={'num':'weave_num'},inplace=True)
    df_batch_detail = df_batch_detail[['batch_id','goods_id','goods_style','plan_num']]
    df_batch_detail = pd.merge(df_num, df_socks, on=['batch_id','goods_id','goods_style'], how='left')

    df_batch_detail = pd.merge(df_batch_detail, df_batch[['batch_id','end_date']], on=['batch_id'], how='left')
    df_batch_detail['weave_num'] = df_batch_detail['weave_num'].fillna(0)
    df_batch_detail['remain_num'] = df_batch_detail['plan_num'] - df_batch_detail['weave_num']
    #df_batch_detail新建一列estimate_days,计算预计天数,即remain_num/capacity，若capacity为0，则设为null
    df_batch_detail['capacity'] = df_batch_detail['capacity'].fillna(0)
    df_batch_detail['device_num'] = df_batch_detail['device_num'].fillna(0)
    df_batch_detail['estimate_days'] = df_batch_detail.apply(lambda row: row['remain_num']/row['capacity'] * 24 if row['capacity'] != 0 else 1000, axis=1)
    #df_batch_detail新建一列remain_days,计算剩余天数,即end_date列值-today，取整数
    df_batch_detail['remain_days'] = (pd.to_datetime(df_batch_detail['end_date']) - pd.to_datetime(today)).dt.days
    #去掉end_date
    df_batch_detail.drop(columns=['end_date'],inplace=True)
    #将该df_batch_detail中的null替换为0
    print("获取数据成功，正在写入。。。",df_batch_detail,df_batch_detail.columns)
    df_batch_detail.to_csv('df_batch_detail.csv',encoding='ANSI')
    try:
        # 创建一个cursor对象
        with connect.cursor() as cursor3:
            # 遍历DataFrame中的每一行
            for index, row in df_batch_detail.iterrows():

                row = row.where(pd.notnull(row), None)
                # 构造查询SQL语句
                sql = f"SELECT COUNT(*) FROM mm_new_batch_weave_progress WHERE batch_id='{row['batch_id']}' AND goods_id='{row['goods_id']}' AND goods_style='{row['goods_style']}'"
                cursor3.execute(sql)
                result = cursor3.fetchone()[0]

                # 如果存在记录，则更新；否则插入
                if result > 0:
                    # 更新SQL语句
                    update_sql = f"UPDATE mm_new_batch_weave_progress SET plan_num=%s, weave_num=%s,capacity=%s,device_num=%s,remain_num=%s,estimate_days=%s,remain_days=%s WHERE batch_id=%s AND goods_id=%s AND goods_style=%s"
                    cursor3.execute(update_sql, (row['plan_num'], row['weave_num'], row['capacity'],row['device_num'],row['remain_num'], row['estimate_days'], row['remain_days'],row['batch_id'], row['goods_id'], row['goods_style']))
                else:
                    # 插入SQL语句
                    insert_sql = f"INSERT INTO mm_new_batch_weave_progress (batch_id, goods_id, goods_style, plan_num, weave_num,capacity,device_num,remain_num,estimate_days,remain_days) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                    cursor3.execute(insert_sql, (row['batch_id'], row['goods_id'], row['goods_style'], row['plan_num'], row['weave_num'], row['capacity'],row['device_num'], row['remain_num'], row['estimate_days'], row['remain_days']))

        # 提交事务
        connect.commit()

    except Exception as e:
        print(f"An error occurred: {e}")

while True:
    connect = conn_db('ryerp_local')  # 建立连接
    try:
        update_progress(connect)
        # 获取当前时间
        now = datetime.now()
        batch_progress(connect, now)
        print(now)
    except Exception as e:
        print(f"An error occurred: {e}")
    finally:
        try:
            connect.close()
        except pymysql.err.Error as e:
            print(f"Error closing connection: {e}")
    time.sleep(600)

